package com.mycompany.inventory.persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.ArrayList;

import com.mycompany.common.AbstractDao;
import com.mycompany.common.jdbc.ConnectionDatabase;
import com.mycompany.inventory.persistence.CustomFieldDAO;
import com.mycompany.inventory.pojo.CustomInfo;
import com.mycompany.inventory.pojo.Product;
import com.mycompany.inventory.pojo.ProductInventory;
import com.mycompany.inventory.pojo.SaleInfo;


/**
 * Created by My Company.
 * User: loitran
 * Date: Feb 29, 2012
 * Time: 1:53:19 PM
 * com.mycompany.inventory.persistence.
 */
public class ProductDAO extends AbstractDao<Product>{
	
	private Connection con = this.connect;
	public static final String SELECT_ALL_SQL = "select * from oce_product order by createdStamp DESC";
	public static final String SELECT_ALL_BY_TYPE_SQL = "SELECT * FROM oce_product WHERE product_type = ? order by createdStamp DESC";
	public static final String SELECT_PRODUCT_VENDOR_SQL = "SELECT c.* FROM oce_vendor a, oce_product_vendor b, oce_product c WHERE c.product_id = b.product_id AND a.vendor_id = b.vendor_id AND b.vendor_id = ?";
	public static final String DELETE_SQL = "DELETE FROM oce_product WHERE product_id = ?";
	public static final String SELECT_SQL_BY_CATEGORY = "SELECT * FROM oce_product WHERE categoryId = ? order by createdStamp DESC";
	public static final String INSERT_SQL = "insert into oce_product (product_id,product_name,product_type,product_description,remarks," +
			   "product_image,costingInfoId, categoryId, cost, customvalue1,customvalue2,customvalue3,customvalue4,customvalue5,customvalue6," +
			   "customvalue7,customvalue8,customvalue9,customvalue10) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	
	public static final String UPDATE_SQL = "UPDATE oce_product SET product_name = ?, product_type = ?, product_description = ?, remarks = ? , " +
		                                	"product_image = ? , costingInfoId = ?, categoryId = ?, cost = ? , customvalue1 = ?, customvalue2 = ?, customvalue3 = ?, customvalue4 = ?, customvalue5 = ?, customvalue6 = ?, customvalue7 = ?, customvalue8 = ?, customvalue9 =?, customvalue10 = ? " +
		                                	"WHERE product_id = ? ";
	public static final String UPDATE_EXTRA_SQL = "UPDATE oce_product SET remarks = ?, storageId = ?, defaultInventoryId = ?, lastVendor = ?, standardUoM = ?, saleUoM = ?, purchaseUoM = ? WHERE product_id = ? ";
	public static final String SELECT_PRODUCT_MATERIAL_SQL = "SELECT p.*,b.number_of_material AS quantity FROM oce_product p,oce_bill_of_material b WHERE p.product_id = b.material_id AND b.product_id = ?";
	public static final String SELECT_PRODUCT_RECEIVE_SQL = "SELECT p.* FROM oce_product p,oce_order_detail o WHERE p.product_id = o.productId AND o.orderId = ? AND o.onOrder = ?";
	public static final String SELECT_PRODUCT_RETURN_SQL = "SELECT p.* FROM oce_product p,oce_order_detail o WHERE p.product_id = o.productId AND o.orderId = ? AND o.onOrder = ?";
	public static final String  SELECT_PRODUCT_PURCHASE_SQL = "SELECT p.* FROM oce_product p,oce_order_detail o WHERE p.product_id = o.productId AND o.orderId = ? AND o.onOrder = ?";
	public static final String SELECT_PRODUCT_REORDER_SQL = "SELECT o.reorder_number FROM oce_product p,oce_storage_info o WHERE p.storageId = o.storageInfoId AND p.product_id = ?";
	public static final String SELECT_PRICE_SQL = "select * from oce_product where product_id = ?";
	
	public Product create(Product obj){
		try{
			
			con.setAutoCommit(false);
			
			if(obj == null || obj.getId() == null || obj.getId().equals(""))
				return obj;				
			
			PreparedStatement ps = con.prepareStatement(INSERT_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			
			long id = 0l;
			ps.setLong(1,id);
			ps.setString(2, obj.getName());
			
			if(obj.getType()== null || obj.getType().getId().equals("")){
				ps.setString(3, null);
			}else{
				ps.setLong(3,Long.parseLong(obj.getType().getId()));
			}
			
			ps.setString(4, obj.getDescription());
			ps.setString(5, obj.getRemark());
			ps.setString(6, obj.getImage());

			if(obj.getCostingInfo()== null || obj.getCostingInfo().getId().equals("")){
				ps.setString(7, null);
			}else{
				ps.setLong(7,Long.parseLong(obj.getCostingInfo().getId()));
			}	

			if(obj.getCategory()== null || obj.getCategory().getId().equals("")){
				ps.setString(8, null);
			}else{
				ps.setLong(8,Long.parseLong(obj.getCategory().getId()));
			}
			ps.setString(9, obj.getCost());	
			
			int idx = 0;
			int databaseIdx = 10;
			for(CustomInfo cus : obj.getCustoms()){
				if(cus.isShowed()){
					ps.setString(databaseIdx,  obj.getCustoms().get(idx).getValue());
					idx++;
					databaseIdx++;
				}
			}
			
			for(int i = databaseIdx ; i< 20 ; i++){
				ps.setString(i, "");
			}
			
			ConnectionDatabase.executeUpdate(ps);
			ResultSet rs=ps.getGeneratedKeys();
            if(rs.next()){
                Integer Id = (Integer) rs.getInt(1);               
                obj.setId(String.valueOf(Id));
            }
            
            SaleInfoDAO saleDao = new SaleInfoDAO();
            for(SaleInfo saleInfo : obj.getSaleinfos()){            	
            	if(saleInfo.getProductId() == null || saleInfo.getProductId().equals(""))
    				saleInfo.setProductId(obj.getId());
    			
    			if(saleInfo.getId() == null || saleInfo.getId().equals("")){				
    				saleDao.create(saleInfo);
    			}else{
    				saleDao.update(saleInfo);
    			}
            }
			
            ProductInventoryDAO vDao = new ProductInventoryDAO();
            for(ProductInventory inv : obj.getInventories()){
            	if(!inv.equals("")){
            		vDao.create(inv);
            	}
            }
            
           //update custom
			CustomFieldDAO cusDao = new CustomFieldDAO();
			idx = 11;
			for(CustomInfo cus : obj.getCustoms()){
				if(cus.isShowed()){
					cus.setId(String.valueOf(idx));
					cusDao.update(cus);
					idx++;					
				}
			}
			
			for(int i = idx ; i< 16 ; i++){				
				cusDao.update(new CustomInfo("", "", String.valueOf(i), "Product", false));
			}
            
			con.commit();
			ps.close();
		}catch (Exception e) {
			// TODO: handle exception
			try{
				con.rollback();
			}catch (SQLException e1){}
			obj = null;
		}finally {
			try{
				con.setAutoCommit(true);
			}catch (SQLException e1){}
		}
		return obj;
	}
	
	public Product update(Product obj){
		try{
			
			con.setAutoCommit(false);
			
			if(obj == null || obj.getId() == null || obj.getId().equals(""))
				return obj;				
			
			PreparedStatement ps = con.prepareStatement(UPDATE_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			
			ps.setString(1, obj.getName());
			
			if(obj.getType()== null || obj.getType().getId().equals("")){
				ps.setString(2, null);
			}else{
				ps.setLong(2,Long.parseLong(obj.getType().getId()));
			}
			
			ps.setString(3, obj.getDescription());
			ps.setString(4, obj.getRemark());
			ps.setString(5, obj.getImage());

			if(obj.getCostingInfo()== null || obj.getCostingInfo().getId().equals("")){
				ps.setString(6, null);
			}else{
				ps.setLong(6,Long.parseLong(obj.getCostingInfo().getId()));
			}	

			if(obj.getCategory()== null || obj.getCategory().getId().equals("")){
				ps.setString(7, null);
			}else{
				ps.setLong(7,Long.parseLong(obj.getCategory().getId()));
			}
			ps.setString(8, obj.getCost());	
			
			int idx = 0;
			int databaseIdx = 9;
			for(CustomInfo cus : obj.getCustoms()){
				if(cus.isShowed()){
					ps.setString(databaseIdx,  obj.getCustoms().get(idx).getValue());
					idx++;
					databaseIdx++;
				}
			}
			
			for(int i = databaseIdx ; i< 19 ; i++){
				ps.setString(i, "");
			}
			
			ps.setLong(19, Long.parseLong(obj.getId()));
			ConnectionDatabase.executeUpdate(ps);
            
			SaleInfoDAO saleDao = new SaleInfoDAO();
            for(SaleInfo saleInfo : obj.getSaleinfos()){            	
            	if(saleInfo.getProductId() == null || saleInfo.getProductId().equals(""))
    				saleInfo.setProductId(obj.getId());
    			
    			if(saleInfo.getId() == null || saleInfo.getId().equals("")){				
    				saleDao.create(saleInfo);
    			}else{
    				saleDao.update(saleInfo);
    			}
            }
			
            ProductInventoryDAO vDao = new ProductInventoryDAO();
            for(ProductInventory inv : obj.getInventories()){
            	if(!inv.equals("")){
            		vDao.create(inv);
            	}
            }
            
          //update custom
			CustomFieldDAO cusDao = new CustomFieldDAO();
			idx = 11;
			for(CustomInfo cus : obj.getCustoms()){
				if(cus.isShowed()){
					cus.setId(String.valueOf(idx));
					cusDao.update(cus);
					idx++;					
				}
			}
			
			for(int i = idx ; i< 16 ; i++){				
				cusDao.update(new CustomInfo("", "", String.valueOf(i), "Product", false));
			}
            
			con.commit();
			ps.close();
		}catch (Exception e) {
			// TODO: handle exception
			try{
				con.rollback();
			}catch (SQLException e1){}
			obj = null;
		}finally {
			try{
				con.setAutoCommit(true);
			}catch (SQLException e1){}
		}
		return obj;
	}
	
	public Product find(long id){
		Product product = new Product();
		try{
			
		}catch (Exception e) {
			// TODO: handle exception
		}
		return product;
	}
	
	public List<Product> findAll(){
		List<Product> products = new ArrayList<Product>();
		try{
			products = this.convert(ConnectionDatabase.executeQuery(this.con.prepareStatement(SELECT_ALL_SQL, PreparedStatement.RETURN_GENERATED_KEYS)));
		}catch (Exception e) {
			// TODO: handle exception
			products = null;
		}
		return products;
	}
	
	public List<Product> getProductCost(long id){
		List<Product> products = new ArrayList<Product>();
		try{
			PreparedStatement ps = con.prepareStatement(SELECT_PRICE_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,id);
			products = convert(ConnectionDatabase.executeQuery(ps));
		}catch (Exception e) {
			// TODO: handle exception
			products = null;
		}
		return products;
	}
	
	public List<Product> findByVendorID(long id){
		List<Product> products = new ArrayList<Product>();
		try{
			PreparedStatement ps = con.prepareStatement(SELECT_PRODUCT_VENDOR_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,id);
			products = convert(ConnectionDatabase.executeQuery(ps));
		}catch (Exception e) {
			// TODO: handle exception
			products = null;
		}
		return products;
	}

	public List<Product> findByType(long id){
		List<Product> products = new ArrayList<Product>();
		try{
			PreparedStatement ps = con.prepareStatement(SELECT_ALL_BY_TYPE_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,id);
			products = convert(ConnectionDatabase.executeQuery(ps));
		}catch (Exception e) {
			// TODO: handle exception
			products = null;
		}
		return products;
	}	
	
	public List<Product> findByMaterialProductID(long id){
		List<Product> products = new ArrayList<Product>();
		try{
			PreparedStatement ps = con.prepareStatement(SELECT_PRODUCT_MATERIAL_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,id);
			products = convert(ConnectionDatabase.executeQuery(ps));
		}catch (Exception e) {
			// TODO: handle exception
			products = null;
		}
		return products;
	}
	
	public String getOrderNumber(long id){
		String orderQuantity = "";
		try{
			PreparedStatement ps = con.prepareStatement(SELECT_PRODUCT_REORDER_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,id);
			ResultSet rs = ConnectionDatabase.executeQuery(ps);
			while(rs.next()){
				orderQuantity = rs.getString("reorder_number");
			}
		}catch (Exception e) {
			// TODO: handle exception
			orderQuantity = "";
		}
		return orderQuantity;
	}	
	
	public List<Product> findProductPurchaseByOrderID(long id, long status){
		List<Product> products = new ArrayList<Product>();
		try{
			PreparedStatement ps;
			ps = con.prepareStatement(SELECT_PRODUCT_RECEIVE_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,id);
			ps.setLong(2, status);			
			products = convert(ConnectionDatabase.executeQuery(ps));
		}catch (Exception e) {
			// TODO: handle exception
			products = null;
		}
		return products;
	}	
	
	public void delete(Product obj){
		try{
			con.setAutoCommit(false);			
		
			PreparedStatement ps = con.prepareStatement(DELETE_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,Long.parseLong(obj.getId()));
			
			ConnectionDatabase.executeUpdate(ps);	
			
			con.commit();
			ps.close();
		}catch (Exception e) {
			// TODO: handle exception
			try{
				con.rollback();
			}catch (SQLException e1){}
			obj = null;
		}finally {
			try{
				con.setAutoCommit(true);
			}catch (SQLException e1){}
		}		
	}
	
	public Product updateExtra(Product product){
		try{
			con.setAutoCommit(false);
			
			StorageInfoDAO dao = new StorageInfoDAO();
			if(product.getStorage()!= null && product.getStorage().getId()!= null && !product.getStorage().getId().equals("")){				
				dao.update(product.getStorage());
			}else{
				dao.create(product.getStorage());
			}				
			
			PreparedStatement ps = con.prepareStatement(UPDATE_EXTRA_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setString(1, product.getRemark());			
			if(product.getStorage().getId().equals("")){
				ps.setLong(2, 0);
			}else{
				ps.setLong(2,Long.parseLong(product.getStorage().getId()));
			}			
			
			if(product.getLocation() == null){
				ps.setLong(3, 0);
			}else{
				ps.setLong(3, Long.parseLong(product.getLocation().getId()));
			}
			
			if(product.getVendor() == null || product.getVendor().getId().equals("")){
				ps.setLong(4, 0);
			}else{
				ps.setLong(4, Long.parseLong(product.getVendor().getId()));
			}
			
			if(product.getStandardUoM() == null || product.getStandardUoM().getId().equals("")){
				ps.setString(5, null);
			}else{
				ps.setString(5, product.getStandardUoM().getId());
			}			

			if(product.getSaleUoM() == null || product.getSaleUoM().getId().equals("")){
				ps.setString(6, null);
			}else{
				ps.setString(6, product.getSaleUoM().getId());
			}
			
			if(product.getPurchaseUoM() == null || product.getPurchaseUoM().getId().equals("")){
				ps.setString(7, null);
			}else{
				ps.setString(7, product.getPurchaseUoM().getId());
			}
			
			ps.setLong(8, Long.parseLong(product.getId()));
									
			ConnectionDatabase.executeUpdate(ps);			
			
			con.commit();
			ps.close();
		}catch (Exception e) {
			try{
				con.rollback();
			}catch (SQLException e1){}
			product = null;
		}finally {
			try{
				con.setAutoCommit(true);
			}catch (SQLException e1){}
		}
	
		return product;
	}
	
	public List<Product> getProductByCategoryId(String categoryId){
		List<Product> listProducts = new ArrayList<Product>();
		try{			
			PreparedStatement ps = con.prepareStatement(SELECT_SQL_BY_CATEGORY, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1, Long.parseLong(categoryId));
			listProducts = this.convert(ConnectionDatabase.executeQuery(ps));
		}catch (Exception e) {
			// TODO: handle exception
			listProducts = null;
		}
		return listProducts;
	}
	
	private List<Product> convert(ResultSet rs) throws SQLException {
		List<Product> products = new ArrayList<Product>();
		TypeDAO typeDao = new TypeDAO();
		ProductCategoriesDAO categoryDao = new ProductCategoriesDAO();
		SaleInfoDAO saleDao = new SaleInfoDAO();
		ProductInventoryDAO invDao = new ProductInventoryDAO();
		CostingInfoDAO cDao = new CostingInfoDAO();
		InventoryDAO inDao = new InventoryDAO();
		VendorDAO vDao = new VendorDAO();
		UnitDAO unitDao = new UnitDAO();
		
		while (rs.next()) {
	        // Get the data from the row using the column index
	        String id = rs.getString(1);
	        String name = rs.getString(2);
	        String typeId = rs.getString(3);
	        String description = rs.getString(4);
	        String remark = rs.getString(5);
	        String image = rs.getString(6);
	        String costingInfoId = rs.getString(7);
	        String categoryId = rs.getString(8);
	        String cost = rs.getString(9);
	        String storageInfoId = rs.getString(10);
	        String defaultInventoryId = rs.getString(11);
	        String lastVendor = rs.getString(12);
	        String standardUoM = rs.getString(13);        
	        String saleUoM = rs.getString(14);
	        String purchaseUoM = rs.getString(15);
	        String customvalue1 = rs.getString(16);
	        String customvalue2 = rs.getString(17);
			
			List<ProductInventory> invs = new ArrayList<ProductInventory>();
			invs = invDao.findByProductID(Long.parseLong(id));

			List<SaleInfo> sales = new ArrayList<SaleInfo>();
	        sales = saleDao.findbyProductId(Long.parseLong(id));
			
	        List<CustomInfo> cus = new ArrayList();
			cus.add(new CustomInfo("Year", customvalue1,"","",true));
			cus.add(new CustomInfo("Model", customvalue2,"","",true));
	        
	        products.add(new Product(id, name, typeId == null ? null : typeDao.find(Long.parseLong(typeId)), description, remark, null, sales,
	        		    invs, categoryId == null ? null : categoryDao.find(Long.parseLong(categoryId)), costingInfoId == null ? null : cDao.find(Long.parseLong(costingInfoId)), cost, storageInfoId, defaultInventoryId == null ? null : inDao.find(Long.parseLong(defaultInventoryId)), lastVendor == null ? null : vDao.find(Long.parseLong(lastVendor)), standardUoM == null ? null : unitDao.find(Long.parseLong(standardUoM)), saleUoM == null ? null : unitDao.find(Long.parseLong(saleUoM)), purchaseUoM == null ? null : unitDao.find(Long.parseLong(purchaseUoM)),cus));
	    }
		rs.close();
		return products;
	}	
}
